#include "DbConnector.h"

int DbConnector::databaseId = 0;

void DbConnector::creatPilipiliDatabase()
{
    QSqlDatabase db;

    if (!db.isValid())
    {
        db = QSqlDatabase::addDatabase("QODBC");
        db.setHostName("127.0.0.1");             // 连接本地主机
        db.setPort(3306);                        // 端口号，默认的
        db.setDatabaseName("MySQL_PiliPiliBuy"); // 数据库名
        db.setUserName("root");                  // 用户
        db.setPassword("abc123");                // 密码
        if (db.open())
        {
            qDebug() << "数据库连接成功。";
        }
        else
        {
            qDebug() << "数据库连接失败：" << db.lastError().text();
        }

        QSqlQuery query;
        QString sql;

        // 登录表 UserCredentials
        sql = "CREATE TABLE IF NOT EXISTS UserCredentials ("
              "user_id BIGINT AUTO_INCREMENT PRIMARY KEY,"
              "email VARCHAR(127) UNIQUE,"
              "phoneNumber VARCHAR(63) UNIQUE,"
              "password VARCHAR(127) NOT NULL);";
        if (!query.exec(sql))
        {
            qWarning() << "登陆表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "登陆表创建成功。";
        }

        // 用户信息表 UserInfo
        sql = "CREATE TABLE IF NOT EXISTS UserInfo("
              "user_id BIGINT PRIMARY KEY,"
              "nickname VARCHAR(255) NOT NULL,"
              "gender VARCHAR(15),"
              "email VARCHAR(127),"
              "signature TEXT,"
              "FOREIGN KEY (user_id) REFERENCES UserCredentials(user_id));";
        if (!query.exec(sql))
        {
            qWarning() << "用户信息表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "用户信息表创建成功。";
        }

        // 商品表 Products
        sql = "CREATE TABLE IF NOT EXISTS Products("
              "product_id BIGINT AUTO_INCREMENT PRIMARY KEY,"
              "type VARCHAR(31) NOT NULL,"
              "product_name VARCHAR(255) NOT NULL,"
              "price DECIMAL(10, 2) NOT NULL,"
              "stock INT NOT NULL DEFAULT 0,"
              "sales INT NOT NULL DEFAULT 0,"
              "date DATE NOT NULL,"
              "filter_criteria VARCHAR(31) NOT NULL,"
              "description TEXT,"
              "CONSTRAINT CHK_Type CHECK (Type = '展会演出' OR Type = '霹雳周边'));";
        if (!query.exec(sql))
        {
            qWarning() << "商品表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "商品表创建成功。";
        }

        // 地址簿表 addressbook
        sql = "CREATE TABLE IF NOT EXISTS AddressBook("
              "address_id BIGINT AUTO_INCREMENT PRIMARY KEY,"
              "user_id BIGINT NOT NULL,"
              "label VARCHAR(63) NOT NULL,"
              "name VARCHAR(63) NOT NULL,"
              "phone VARCHAR(63) NOT NULL,"
              "address TEXT NOT NULL,"
              "FOREIGN KEY (user_id) REFERENCES UserCredentials(user_id));";
        if (!query.exec(sql))
        {
            qWarning() << "地址簿表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "地址簿表创建成功。";
        }

        // 订单表 orders
        sql = "CREATE TABLE IF NOT EXISTS Orders("
              "order_id BIGINT AUTO_INCREMENT PRIMARY KEY,"
              "user_id BIGINT NOT NULL,"
              "order_date DATETIME NOT NULL,"
              "total_amount DECIMAL(10, 2) NOT NULL,"
              "name VARCHAR(63) NOT NULL,"
              "phone VARCHAR(63) NOT NULL,"
              "address TEXT NOT NULL,"
              "FOREIGN KEY (user_id) REFERENCES UserCredentials(user_id));";
        if (!query.exec(sql))
        {
            qWarning() << "订单表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "订单表创建成功。";
        }

        // 订单详情表 orderdetails
        sql = "CREATE TABLE IF NOT EXISTS OrderDetails("
              "order_detail_id BIGINT AUTO_INCREMENT PRIMARY KEY,"
              "order_id BIGINT NOT NULL,"
              "product_id BIGINT NOT NULL,"
              "quantity INT NOT NULL,"
              "price DECIMAL(10, 2) NOT NULL,"
              "FOREIGN KEY (order_id) REFERENCES Orders(order_id),"
              "FOREIGN KEY (product_id) REFERENCES Products(product_id))";
        if (!query.exec(sql))
        {
            qWarning() << "订单详情表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "订单详情表创建成功。";
        }

        // 商品评论表 productsReview
        sql = "CREATE TABLE IF NOT EXISTS ProductReviews("
              "review_id BIGINT AUTO_INCREMENT PRIMARY KEY,"
              "product_id BIGINT NOT NULL,"
              "user_id BIGINT NOT NULL,"
              "content TEXT NOT NULL,"
              "review_date DATE NOT NULL,"
              "FOREIGN KEY (product_id) REFERENCES Products(product_id),"
              "FOREIGN KEY (user_id) REFERENCES UserCredentials(user_id))";
        if (!query.exec(sql))
        {
            qWarning() << "商品评论表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "商品评论表创建成功。";
        }

        // 消息表 Messages
        sql = "CREATE TABLE IF NOT EXISTS Messages("
              "message_id BIGINT AUTO_INCREMENT PRIMARY KEY,"
              "sender_id BIGINT NOT NULL,"
              "receiver_id BIGINT NOT NULL,"
              "content TEXT NOT NULL,"
              "send_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);";
        if (!query.exec(sql))
        {
            qWarning() << "消息表创建失败：" << query.lastError().text();
        }
        else
        {
            qDebug() << "消息表创建成功。";
        }
    }

    if (db.isOpen())
        db.close();
}

QSqlDatabase DbConnector::getDatabase()
{
    if (!newDb.isValid())
    {
        newDb = QSqlDatabase::addDatabase("QODBC", QString::number(++DbConnector::databaseId));
        newDb.setHostName("127.0.0.1");             // 连接本地主机
        newDb.setPort(3306);                        // 端口号，默认的
        newDb.setDatabaseName("MySQL_PiliPiliBuy"); // 数据库名
        newDb.setUserName("root");                  // 用户
        newDb.setPassword("abc123");                // 密码
        if (newDb.open())
        {
            // qDebug() << "数据库连接成功：" << DbConnector::databaseId;
        }
        else
        {
            qDebug() << "数据库连接失败：" << newDb.lastError().text();
        }
    }
    return newDb;
}

DbConnector::~DbConnector()
{
    if (newDb.isOpen())
        newDb.close();
}
